package aes.server;

import java.io.*;
import java.sql.*;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;

import common.*;

/**
 * This class represents manager used in order to manage exam executions
 *
 */
public class ExamExecutionManager {

	/**
	 * Add exam execution to DB
	 * @param Exam Execution
	 * @return true-can add Exam Execution, false- can't exam execution
	 */
	public static boolean addExamExecution(ExamExecution ex){
		DBManager db = DBManager.getInstance();
		try {
			String sql ="INSERT INTO aes.exam_execution (`code`, `autoExamId`, `definedTime`, `actualTime`, `examDate`, `examFile`, `execTeacherId`) "+
					"VALUES(?,?,?,?,?,?);";

			if (ex instanceof ManualExamExecution) {
				sql ="INSERT INTO aes.exam_execution (`code`, `definedTime`, `actualTime`, `examDate`, `examFile`, `execTeacherId`) "+
						"VALUES(?,?,?,?,?,?);";
				PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
				stmt1.setString(1, ex.getCode());
				stmt1.setInt(2, ex.getDefinedTime());
				stmt1.setInt(3, ex.getDefinedTime());
				stmt1.setTimestamp(4, new Timestamp(ex.getExecutionDate().getTime()));
				stmt1.setString(5, ((ManualExamExecution)ex).getFileName());
				stmt1.setInt(6, ex.getExecTeacherID());
				stmt1.executeUpdate();
			}
			else if (ex instanceof AutoExamExecution) {
				sql ="INSERT INTO aes.exam_execution (`code`, `autoExamId`, `definedTime`, `actualTime`, `examDate`, `minCheatingErrors`, `execTeacherId`) "+
						"VALUES(?,?,?,?,?,?,?);";
				PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
				stmt1.setString(1, ex.getCode());
				stmt1.setInt(2, ((AutoExamExecution) ex).getExam());
				stmt1.setInt(3, ex.getDefinedTime());
				stmt1.setInt(4, ex.getDefinedTime());
				stmt1.setTimestamp(5, new Timestamp(ex.getExecutionDate().getTime()));
				stmt1.setInt(6, ((AutoExamExecution) ex).getMinCheatingErrors());
				stmt1.setInt(7, ex.getExecTeacherID());
				stmt1.executeUpdate();
			}

			return true;
		}
		catch(SQLException ex1){
			return false;
		}
	}

	/**
	 * Teacher sends request to change time solving
	 * @param code Exam execution code
	 * @param newTime Added time
	 * @param notes Notes
	 * @param teacherId Requesting teacher Id
	 * @return Operation success or failed
	 */
	public static boolean requestTimeChange(String code,int newTime,String notes, int teacherId){
		DBManager db = DBManager.getInstance();		
		String sql ="UPDATE aes.exam_execution "+
				"SET status=?, adittionalTime=?, notes=?  "+ 
				"WHERE code=? AND status=0 AND execTeacherId=?";
		try {
			PreparedStatement stmt = db.getConnection().prepareStatement(sql);
			stmt.setInt(1, 1);
			stmt.setInt(2, newTime);
			stmt.setString(3, notes);
			stmt.setString(4, code);
			stmt.setInt(5, teacherId);
			return stmt.executeUpdate()>0;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	/**Principal response to Teacher request to change solve time of exam
	 * @param code Exam execution code
	 * @param accepted Time change accepted flag
	 * @return -1 on error, 0 on denied, otherwise accepted change time
	 */
	public static int responseTimeChange(String code,boolean accepted, int[] OwnerTeacherId){

		DBManager db = DBManager.getInstance();		
		if(accepted==true){	
			String sql ="UPDATE aes.exam_execution "+
					"SET status=2, actualTime = adittionalTime + actualTime "+
					"WHERE code=?";
			try {
				PreparedStatement stmt = db.getConnection().prepareStatement(sql);
				stmt.setString(1, code);
				stmt.executeUpdate();

				sql = "SELECT adittionalTime, execTeacherId FROM aes.exam_execution WHERE code=?;";
				stmt = db.getConnection().prepareStatement(sql);
				stmt.setString(1, code);

				ResultSet rs1 = stmt.executeQuery();
				if (rs1.next()) {
					OwnerTeacherId[0] = new Integer(rs1.getInt("execTeacherId"));
					return rs1.getInt("adittionalTime");
				}
				return -1;
			} catch (SQLException e) {
				e.printStackTrace();
				return -1;
			}
		}
		else{
			String sql1 ="UPDATE aes.exam_execution "+
					"SET status=3  "+
					"WHERE code=?";
			try {
				PreparedStatement stmt1 = db.getConnection().prepareStatement(sql1);
				stmt1.setString(1, code);
				stmt1.executeUpdate();
				return 0;
			} catch (SQLException e) {
				e.printStackTrace();
				return -2;
			}	
		}
	}

	/**
	 * Teacher 	locks exam
	 * @param code Exam execution code
	 * @param teacherId Id of the teacher
	 * @return true- exam locking , false- exam locking failed
	 */
	public static boolean examLock(String code, int teacherId){
		DBManager db = DBManager.getInstance();		

		String sql ="UPDATE aes.exam_execution "+
				"SET lockTime = NOW(), status=4 "+
				"WHERE code=? AND status<>4 AND execTeacherId=?;";
		try {
			PreparedStatement stmt = db.getConnection().prepareStatement(sql);
			stmt.setString(1, code);
			stmt.setInt(2, teacherId);
			return stmt.executeUpdate()>0;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * Check existence of exam code.
	 * @param Exam code.
	 * @return false - exam code not exist, true- exam code exists
	 */
	public static boolean checkExistenceOfCode(String code){
		DBManager db = DBManager.getInstance();	
		String sql="SELECT code "+
				"FROM aes.exam_execution "+
				"WHERE code=?";
		try {
			PreparedStatement stmt = db.getConnection().prepareStatement(sql);
			stmt.setString(1, code); 
			ResultSet rs1 = stmt.executeQuery();
			boolean res;
			if (!rs1.next() || (rs1.getString("code")) == null)
				res = false;
			else 
				res = true;
			rs1.close();
			return res;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * Get List of exam codes which time was changed
	 * @return List of exam codes which time was changed
	 */
	public static String[] getCodeListOfTimeChancges(){
		ArrayList<String> arr=new ArrayList<String>();
		String s;
		DBManager db = DBManager.getInstance();	
		String sql="SELECT code "+
				"FROM aes.exam_execution "+
				"WHERE status=1";
		try {
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			ResultSet rs1 = stmt1.executeQuery();
			while(rs1.next()){
				s=rs1.getString("code");
				arr.add(s);
			}
			rs1.close();
			String[] ret = new String[arr.size()];
			arr.toArray(ret);
			return ret;	
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * Get notes- explanation about adittional time
	 * @param Exam execution code
	 * @return Notes
	 */
	public static String getNotes(String code){
		DBManager db = DBManager.getInstance();	
		String note;
		try {
			String sql="SELECT notes FROM aes.exam_execution WHERE code=?";
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setString(1,code);
			ResultSet rs1 = stmt1.executeQuery();
			if (!rs1.next() || (rs1.getString("notes")) == null)
				return null;
			note=rs1.getString("notes");
			rs1.close();

			return note;

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}


	}

	/**
	 * Get additional time which requested by teacher by exam execution code.
	 * @param Exam execution code
	 * @return -1 not exists, time if exists
	 */
	public static int getAditionalTime(String code){
		DBManager db = DBManager.getInstance();	
		int t;
		try {
			String sql="SELECT adittionalTime FROM aes.exam_execution WHERE code = ?";
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setString(1,code);
			ResultSet rs1 = stmt1.executeQuery();
			if (!rs1.next() || (rs1.getInt("adittionalTime")) == 0)
				return 0;
			t=rs1.getInt("adittionalTime");
			rs1.close();	
			return t;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}

	}

	/**
	 * Start student exam execution
	 * @param code Exam code
	 * @param userId Student Id
	 * @return Exam Type (Manual or Computorized)
	 */
	public static int StartExam(String code, int userId) {
		DBManager db = DBManager.getInstance();	
		try {
			int exam_type;
			String sql = "SELECT * FROM aes.exam_execution WHERE code=? AND status<>4 AND lockTime IS NULL;";
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setString(1,code);
			ResultSet rs1 = stmt1.executeQuery();
			if (!rs1.next())
				return -1;
			exam_type = (rs1.getObject("autoExamId") != null && rs1.getInt("autoExamId")>0)? 1:2;
			Date d = new Date(rs1.getTimestamp("examDate").getTime());
			int exam_minutes = rs1.getInt("definedTime");
			if (rs1.getInt("actualTime")>0)
				exam_minutes = rs1.getInt("actualTime");
			Calendar cal = Calendar.getInstance();
			cal.setTime(d);
			cal.add(Calendar.MINUTE, exam_minutes);
			java.util.Date now = new java.util.Date();
			if (now.before(d) || now.after(cal.getTime()))
				return -1;
			// Can execute exam.
			rs1.close();

			// Check if already start time is stored.
			sql = "SELECT * FROM aes.student_exam WHERE studentId=? AND examCode=?";
			stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setInt(1,userId);
			stmt1.setString(2,code);

			rs1 = stmt1.executeQuery();
			if (!rs1.next()) {
				// Save student's exam execution start time.
				rs1.close();
				sql = "INSERT INTO aes.student_exam (`studentId`, `examCode`, `timeStarted`) VALUES (?, ?, NOW());";
				stmt1 = db.getConnection().prepareStatement(sql);
				stmt1.setInt(1,userId);
				stmt1.setString(2,code);
				stmt1.executeUpdate();
			}
			else {
				// Already started exam. Can't view only if is not checked and is finished. 
				if (!rs1.getBoolean("isApproved") && rs1.getObject("timeFinished") != null)
					return -2;
			}
			return exam_type;
		}
		catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
	}

	/**
	 * Retrieves student exam
	 * @param code Execution code
	 * @param studentId Student Id
	 * @return Student exam object
	 */
	public static StudentExam getStudentExam(String code, int studentId) {
		DBManager db = DBManager.getInstance();	
		String sql = "SELECT * FROM aes.exam_execution ee LEFT OUTER JOIN aes.exams e ON ee.autoExamId=e.eId WHERE ee.code=?;";
		try {
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setString(1,code);
			ResultSet rs1 = stmt1.executeQuery();
			if (!rs1.next())
				return null;
			// Get exam.
			StudentExam ret = new StudentExam();
			ret.setStudentId(studentId);
			if (rs1.getObject("autoExamId") != null) {
				// Computerized exam.
				AutoExamExecution ae = new AutoExamExecution();
				ae.setCode(code);
				ae.setExam(rs1.getInt("autoExamId"));
				ae.setStudentsNotes(rs1.getString("eComments"));
				ae.setTeachersNotes(rs1.getString("tComments"));

				ae.setDefinedTime(rs1.getInt("definedTime"));
				if (rs1.getObject("lockTime") != null)
					ae.setLockTime(new Date(rs1.getTimestamp("lockTime").getTime()));
				ae.setExecutionDate(new Date(rs1.getTimestamp("examDate").getTime()));
				if (rs1.getObject("minCheatingErrors") != null)
					ae.setMinCheatingErrors(rs1.getInt("minCheatingErrors"));
				ae.setExecTeacherID(rs1.getInt("execTeacherId"));
				Calendar cal = Calendar.getInstance();
				cal.setTime(new Date(rs1.getTimestamp("examDate").getTime()));
				cal.add(Calendar.MINUTE, rs1.getInt("actualTime"));
				java.util.Date now = new java.util.Date();
				ae.setActualTime((int) ((cal.getTimeInMillis() - now.getTime()) / 1000));
				ret.setExam(ae);

				// Get questions.
				rs1.close();
				ret.setQuestions(QuestionsManager.getExamQuestions(ae.getExam()));

				// Get student answers.
				ret.setAnswers(getExamStudentsAnswers(studentId, ae.getCode()));
				/*
				ArrayList<StudentAnswer> arr=new ArrayList<StudentAnswer>();
				sql = "SELECT * FROM aes.students_answers WHERE studentId=? AND eCode=?";
				stmt1 = db.getConnection().prepareStatement(sql);
				stmt1.setInt(1,studentId);
				stmt1.setString(2,ae.getCode());
				rs1 = stmt1.executeQuery();
				while (rs1.next()) {
					StudentAnswer answer = new StudentAnswer();
					answer.setExamCode(ae.getCode());
					answer.setStudentId(studentId);
					answer.setQuestionId(rs1.getInt("qId"));
					if (rs1.getObject("answer") != null)
						answer.setAnswer(rs1.getInt("answer"));
					arr.add(answer);
				}
				rs1.close();
				StudentAnswer[] sa = new StudentAnswer[arr.size()];
				arr.toArray(sa);
				ret.setAnswers(sa);
				 */
			}
			else {
				// Manual exam.
				ManualExamExecution me = new ManualExamExecution();
				me.setCode(code);
				Calendar cal = Calendar.getInstance();
				cal.setTime(new Date(rs1.getTimestamp("examDate").getTime()));
				cal.add(Calendar.MINUTE, rs1.getInt("actualTime"));
				java.util.Date now = new java.util.Date();
				me.setActualTime((int) ((cal.getTimeInMillis() - now.getTime()) / 1000));
				me.setDefinedTime(rs1.getInt("definedTime"));
				me.setExecTeacherID(rs1.getInt("execTeacherId"));
				if (rs1.getObject("lockTime") != null)
					me.setLockTime(new Date(rs1.getTimestamp("lockTime").getTime()));
				me.setExecutionDate(new Date(rs1.getTimestamp("examDate").getTime()));
				me.setFileName(rs1.getString("examFile"));

				// Read file.
				byte[] data;
				try {
					FileInputStream fin = new FileInputStream(System.getProperty("user.dir") + "\\AES\\" + me.getFileName());
					data = new byte[fin.available()];
					fin.read(data);
					fin.close();
					me.setFileData(data);
				}
				catch(FileNotFoundException ex1)
				{
					return null;
				}
				catch(IOException ioe)
				{
					return null;
				}
				
				// Get answers file if avaliable.
				byte[] answer;
				try {
					String[] tmp = me.getFileName().trim().split("\\.");
					if (tmp.length == 2) {
						FileInputStream fin = new FileInputStream(System.getProperty("user.dir") + "\\AES\\Answers\\" + me.getCode() + "\\" + studentId + "." + tmp[1]);
						answer = new byte[fin.available()];
						fin.read(answer);
						fin.close();
						me.setAnswersFileData(answer);
					}
				}
				catch(FileNotFoundException ex1)
				{
					
				}
				catch(IOException ioe)
				{
					return null;
				}
				ret.setExam(me);
			}

			// Get execution parameters.
			rs1.close();

			sql = "SELECT * FROM aes.student_exam WHERE examCode=? AND studentId=?;";
			stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setString(1,code);
			stmt1.setInt(2, studentId);
			rs1 = stmt1.executeQuery();
			if (!rs1.next())
				return null;
			ret.setTimeStarted(new Date(rs1.getTimestamp("timeStarted").getTime()));
			if (rs1.getObject("timeFinished") != null)
				ret.setTimeFinished(new Date(rs1.getTimestamp("timeFinished").getTime()));
			if (rs1.getObject("grade") != null)
				ret.setGrade(rs1.getInt("grade"));
			if (rs1.getObject("notes") != null)
				ret.setNotes(rs1.getString("notes"));
			ret.setApproved(rs1.getBoolean("isApproved"));
			ret.setCheater(rs1.getBoolean("isCheater"));
			rs1.close();
			return ret;
		}
		catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * Store student answers
	 * @param exam Student exam
	 * @param hasFinished Student has finished exam
	 * @return Store result
	 */
	public static int submitStudentAnswers(StudentExam exam, boolean hasFinished) {
		DBManager db = DBManager.getInstance();	
		String sql = "DELETE FROM aes.students_answers WHERE studentId=? AND eCode=?; ";
		try {
			int grade = 0;
			PreparedStatement stmt1;
			if (exam.getExam() instanceof AutoExamExecution) {
				// Store students questions answers.
				stmt1 = db.getConnection().prepareStatement(sql);
				stmt1.setInt(1, exam.getStudentId());
				stmt1.setString(2, exam.getExam().getCode());
				stmt1.executeUpdate();
				if (exam.getAnswers() != null) {
					for (StudentAnswer sa : exam.getAnswers()) {
						sql = "INSERT INTO aes.students_answers (`studentId`, `eCode`, `qId`, `answer`) VALUES (";
						sql += sa.getStudentId() + ", " + sa.getExamCode() + ", " + sa.getQuestionId() + ", " + sa.getAnswer() + "); ";
						stmt1 = db.getConnection().prepareStatement(sql);
						stmt1.executeUpdate();
						// Calculate grade.
						for (Question q : exam.getQuestions()) {
							if (q.getId() == sa.getQuestionId()) {
								if (q.getCorrectAnswer() == sa.getAnswer())
									grade += q.getPoints();
								break;
							}
						}
					}
				}
			}

			if (hasFinished)
				sql = "UPDATE aes.student_exam SET `timeFinished`=NOW(), `grade`=? WHERE studentId=? AND examCode=?;";
			else
				sql = "UPDATE aes.student_exam SET `grade`=? WHERE studentId=? AND examCode=?;";
			stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setInt(1, grade);
			stmt1.setInt(2, exam.getStudentId());
			stmt1.setString(3, exam.getExam().getCode());
			stmt1.executeUpdate();
			return grade;
		}
		catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
	}

	/**
	 * Scans and closes exams that need to be closed
	 */
	public static void closeExams() {
		DBManager db = DBManager.getInstance();	
		String sql = "UPDATE aes.exam_execution SET `status`=4 WHERE DATE_ADD(examDate,INTERVAL actualTime MINUTE) < NOW() AND status<>4;";
		try {
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.executeUpdate();
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Check computerized exams for cheating.
	 */
	public static void checkExams() {
		DBManager db = DBManager.getInstance();	
		String sql = "SELECT * FROM aes.exam_execution WHERE autoExamId IS NOT NULL AND status=4 AND isChecked=0;";
		try {
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			ResultSet rs = stmt1.executeQuery();

			// Get exams.
			ArrayList<AutoExamExecution> arr=new ArrayList<AutoExamExecution>();
			while(rs.next()){
				AutoExamExecution ee = new AutoExamExecution();
				ee.setCode(rs.getString("code"));
				ee.setExam(rs.getInt("autoExamId"));
				ee.setMinCheatingErrors(rs.getInt("minCheatingErrors"));
				arr.add(ee);
			}
			rs.close();

			for (AutoExamExecution aee : arr) {
				// Get student Exams.
				StudentExam[] ses = getStudentExams(aee.getCode());

				// Check for same errors.
				for (StudentExam se: ses) {
					// Find same errors.
					if (FindSameErrors(aee.getCode(), se.getStudentId()) >= aee.getMinCheatingErrors()) {
						// Mark as cheater.
						sql = "UPDATE aes.student_exam SET isCheater=1 WHERE studentId=? AND examCode=?;";
						stmt1 = db.getConnection().prepareStatement(sql);
						stmt1.setInt(1, se.getStudentId());
						stmt1.setString(2, aee.getCode());
						stmt1.executeUpdate();
					}
				}
				sql = "UPDATE aes.exam_execution SET `isChecked`=1 WHERE code=?;";
				stmt1 = db.getConnection().prepareStatement(sql);
				stmt1.setString(1, aee.getCode());
				stmt1.executeUpdate();
			}
			
			
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Finds wrong answers of other students on the same exam 
	 * @param executionCode Exam execution code
	 * @param studentId Student Id
	 * @return Number of the same wrong answers
	 */
	private static int FindSameErrors(String executionCode, int studentId) {
		DBManager db = DBManager.getInstance();	
		String sql = "SELECT count(oa.qId) as wrongAnswers " +
				"FROM (SELECT sa.qId, sa.answer " + 
				"FROM aes.students_answers sa, aes.questions q " + 
				"WHERE q.qId=sa.qId AND q.correct!=sa.answer AND sa.eCode=? AND sa.studentId=?) ma, " +
				"(SELECT sa.qId, sa.studentId, sa.answer " +
				"FROM aes.students_answers sa " +
				"WHERE sa.studentId!=?) oa " +
				"WHERE ma.answer=oa.answer AND ma.qId=oa.qId;";
		try {
			PreparedStatement stmt = db.getConnection().prepareStatement(sql);
			stmt.setString(1, executionCode);
			stmt.setInt(2, studentId);
			stmt.setInt(3, studentId);
			ResultSet rs = stmt.executeQuery();
			if (rs.next()) {
				return rs.getInt("wrongAnswers");
			}
			rs.close();
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}

	/**
	 * Retrieve student exams of exam execution
	 * @param executionCode Exam execution code
	 * @return Array of student exams
	 */
	public static StudentExam[] getStudentExams(String executionCode) {
		DBManager db = DBManager.getInstance();	
		String sql = "SELECT * FROM aes.exam_execution WHERE code=?;";
		try {
			ExamExecution ee;
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setString(1, executionCode);
			ResultSet rs = stmt1.executeQuery();
			if (!rs.next())
				return null;
			if (rs.getObject("autoExamId") != null) {
				// Computorized exam.
				AutoExamExecution ae = new AutoExamExecution();
				ae.setCode(executionCode);
				ae.setExam(rs.getInt("autoExamId"));

				ae.setDefinedTime(rs.getInt("definedTime"));
				if (rs.getObject("lockTime") != null)
					ae.setLockTime(new Date(rs.getTimestamp("lockTime").getTime()));
				ae.setExecutionDate(new Date(rs.getTimestamp("examDate").getTime()));
				if (rs.getObject("minCheatingErrors") != null)
					ae.setMinCheatingErrors(rs.getInt("minCheatingErrors"));

				Calendar cal = Calendar.getInstance();
				cal.setTime(new Date(rs.getTimestamp("examDate").getTime()));
				cal.add(Calendar.MINUTE, rs.getInt("actualTime"));
				java.util.Date now = new java.util.Date();
				ae.setActualTime((int) ((cal.getTimeInMillis() - now.getTime()) / 1000));
				ee = ae;
			}
			else {
				// Manual exam.
				ManualExamExecution me = new ManualExamExecution();
				me.setCode(executionCode);
				Calendar cal = Calendar.getInstance();
				cal.setTime(new Date(rs.getTimestamp("examDate").getTime()));
				cal.add(Calendar.MINUTE, rs.getInt("actualTime"));
				java.util.Date now = new java.util.Date();
				me.setActualTime((int) ((cal.getTimeInMillis() - now.getTime()) / 1000));
				me.setDefinedTime(rs.getInt("definedTime"));
				if (rs.getObject("lockTime") != null)
					me.setLockTime(new Date(rs.getTimestamp("lockTime").getTime()));
				me.setExecutionDate(new Date(rs.getTimestamp("examDate").getTime()));
				me.setFileName(rs.getString("examFile"));

				// Read file.
				byte[] data;
				try {
					FileInputStream fin = new FileInputStream(System.getProperty("user.dir") + "\\AES\\" + me.getFileName());
					data = new byte[fin.available()];
					fin.read(data);
					fin.close();
					me.setFileData(data);
				}
				catch(FileNotFoundException ex1)
				{
					return null;
				}
				catch(IOException ioe)
				{
					return null;
				}
				ee = me;
			}
			rs.close();
			sql = "SELECT * FROM aes.student_exam WHERE examCode=?;";
			stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setString(1, executionCode);
			rs = stmt1.executeQuery();
			ArrayList<StudentExam> exams=new ArrayList<StudentExam>();
			while(rs.next()){
				StudentExam exam = new StudentExam();
				exam.setStudentId(rs.getInt("studentId"));
				exam.setTimeStarted(new Date(rs.getTimestamp("timeStarted").getTime()));
				if (rs.getObject("timeFinished") != null)
					exam.setTimeFinished(new Date(rs.getTimestamp("timeFinished").getTime()));
				if (rs.getObject("grade") != null)
					exam.setGrade(rs.getInt("grade"));
				if (rs.getObject("notes") != null)
					exam.setNotes(rs.getString("notes"));
				exam.setApproved(rs.getBoolean("isApproved"));
				exam.setCheater(rs.getBoolean("isCheater"));
				exams.add(exam);
			}
			rs.close();
			for (StudentExam exam: exams) {
				exam.setAnswers(getExamStudentsAnswers(exam.getStudentId(), executionCode));
				exam.setExam(ee);
			}

			StudentExam[] se = new StudentExam[exams.size()];
			exams.toArray(se);
			return se;
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Retrieve student answers of exam execution
	 * @param executionCode Exam execution code
	 * @return Array of student answers
	 */
	public static StudentAnswer[] getExamStudentsAnswers(int studentId, String executionCode) {
		DBManager db = DBManager.getInstance();	
		ArrayList<StudentAnswer> arr=new ArrayList<StudentAnswer>();
		String sql = "SELECT * FROM aes.students_answers WHERE studentId=? AND eCode=?";
		try {
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setInt(1, studentId);
			stmt1.setString(2, executionCode);
			ResultSet rs1 = stmt1.executeQuery();
			while (rs1.next()) {
				StudentAnswer answer = new StudentAnswer();
				answer.setExamCode(executionCode);
				answer.setStudentId(studentId);
				answer.setQuestionId(rs1.getInt("qId"));
				if (rs1.getObject("answer") != null)
					answer.setAnswer(rs1.getInt("answer"));
				arr.add(answer);
			}
			rs1.close();
			StudentAnswer[] sa = new StudentAnswer[arr.size()];
			arr.toArray(sa);
			return sa;
		}
		catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * Get students list by exam execution code
	 * @param Exam execution code
	 * @return Students list that finished exam
	 */
	public static Integer[] getStudentsList(String code){
		ArrayList<Integer> arr=new ArrayList<Integer>();
		Integer i;
		DBManager db = DBManager.getInstance();	
		String sql ="SELECT studentId " +
				"FROM aes.student_exam "+ 
				"WHERE isApproved=0 AND examCode=? AND timeFinished is not NULL";

		try {
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setString(1,code);
			ResultSet rs1 = stmt1.executeQuery();
			while(rs1.next()){
				i=rs1.getInt("studentId");
				arr.add(i);
			}
			rs1.close();
			Integer[] ret = new Integer[arr.size()];
			arr.toArray(ret);
			return ret;	
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * Get exams executed which teacherID created for report teacher UI table
	 * @param teacherID
	 * @return Executed exam by teacherID who created them
	 */
	public static ArrayList<Integer[]> getExamList(int teacherID){
		ArrayList<Integer[]> arr=new ArrayList<Integer[]>();
		DBManager db = DBManager.getInstance();
		try {
			String sql ="SELECT ee.code, e.eId " +
						"FROM exams e, exam_execution ee , users u "+
						"Where e.eId=ee.autoExamId and ee.status=4 and u.id=e.teacherID and u.role=2 and e.teacherID=?" ;
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setInt(1,teacherID);
			ResultSet rs1 = stmt1.executeQuery();
			while(rs1.next()){
				Integer[] arrInstance = new Integer[2];
				arrInstance[0]=(rs1.getInt("code"));
				arrInstance[1]=(rs1.getInt("eId"));
				arr.add(arrInstance);
			}
			rs1.close();
			return arr;
		}
		catch(SQLException ex){
			return null;
		}
	}
	/**
	 * Get exam info for teacher report on exams
	 * @param examID
	 * @return array of strings who holds info for exam report screen
	 */
	public static String[] getExamInfo(int examID){
		String[] info=new String[20];
		DBManager db = DBManager.getInstance();
		try {
			String sql ="SELECT ee.code as executionCode ,ee.examDate as date, ee.execTeacherId as executorId, d.dName as subject, c.cName as course,  ee.autoExamId%100 as examId, ee.actualTime " +
						"FROM exam_execution ee, departments d, courses c " +
						"WHERE (ee.autoExamId/10000)-(ee.autoExamId%10000)/10000=d.dId and ((ee.autoExamId/100)-(ee.autoExamId%100)/100)%100=c.cId and ee.code=?";
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setInt(1,examID);
			ResultSet rs1 = stmt1.executeQuery();
			rs1.next();
			info[0]=Integer.toString(rs1.getInt("executionCode"));
			Date d=new Date(rs1.getTimestamp("date").getTime());
			Format formatter = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
			info[1]=formatter.format(d);
			info[2]=Integer.toString(rs1.getInt("executorId"));
			info[3]=rs1.getString("subject");
			info[4]=rs1.getString("course");
			info[5]=Integer.toString(rs1.getInt("examId"));
			info[6]=Integer.toString(rs1.getInt("actualTime"));
			rs1.close();
			String sq2 = "select (q2.c2/q1.c1*100) as didntMakeIt " + 
						 "from (SELECT count(se1.studentId) as c1 FROM exam_execution ee, student_exam se1 WHERE ee.code=? and ee.code=se1.examCode and  se1.timeStarted IS NOT NULL and se1.timeFinished is not null and se1.isApproved=1 and se1.isCheater=0 ) as q1, (SELECT count(se1.studentId) as c2 FROM exam_execution ee, student_exam se1 WHERE ee.code=? and ee.code=se1.examCode and  se1.timeStarted IS NOT NULL and se1.timeFinished is null and  se1.isCheater=0 and  se1.isApproved=1 ) as q2";
			stmt1 = db.getConnection().prepareStatement(sq2);
			stmt1.setInt(1,examID);
			stmt1.setInt(2,examID);
			rs1 = stmt1.executeQuery();
			rs1.next();
			float temp=rs1.getFloat("didntMakeIt");
			info[7]=Float.toString(temp) + " %"; //didnt make it %
			rs1.close();///
			info[8]=Float.toString(100-temp) + " %"; //finished on time %
			String sq3 ="SELECT studentId ,grade "+
					"FROM student_exam se "+
					"where se.examCode=? and se.isApproved=1";
			stmt1 = db.getConnection().prepareStatement(sq3);
			stmt1.setString(1,String.valueOf(examID));
			rs1 = stmt1.executeQuery();
			ArrayList<Integer> arr=new ArrayList<Integer>();
			while(rs1.next()){
				arr.add(rs1.getInt("grade"));
			}
			rs1.close();
			int sum=0,count=0;
			Iterator<Integer> itr = (Iterator<Integer>)arr.iterator();
			while(itr.hasNext()){
				count++;
				sum+=itr.next().intValue();
			}
			rs1.close();///
			info[9]=Float.toString(sum/count); //mean
			info[10]=Integer.toString(arr.get(((int)arr.size()/2)).intValue());//median
			for(int i=11; i<20;i++){
			info[i]=Integer.toString(0); //0-54.9
			}
			itr=arr.iterator();
			while(itr.hasNext()){
				int x=itr.next().intValue();
				if(x>=0 && x<=54.9)
					info[11]=Integer.toString((Integer.parseInt(info[11])+1));
				else if(x>=55 && x<=64)
					info[12]=Integer.toString((Integer.parseInt(info[12])+1));
				else if(x>=65 && x<=69)
					info[13]=Integer.toString((Integer.parseInt(info[13])+1));
				else if(x>=70 && x<=74)
					info[14]=Integer.toString((Integer.parseInt(info[14])+1));
				else if(x>=75 && x<=79)
					info[15]=Integer.toString((Integer.parseInt(info[15])+1));
				else if(x>=80 && x<=84)
					info[16]=Integer.toString((Integer.parseInt(info[16])+1));
				else if(x>=85 && x<=89)
					info[17]=Integer.toString((Integer.parseInt(info[17])+1));
				else if(x>=90 && x<=94)
					info[18]=Integer.toString((Integer.parseInt(info[18])+1));
				else if(x>=95 && x<=100)
					info[19]=Integer.toString((Integer.parseInt(info[19])+1));
			}
			return info;
		}
		catch(SQLException ex){
			return null;
		}
	}
	/**
	 * Retrieve student exam executions
	 * @param studentId Student Id
	 * @return StudentExam objects array
	 */
	public static StudentExam[] getStudentExamExecutions(int studentId) {
		ArrayList<StudentExam> arr=new ArrayList<StudentExam>();
		DBManager db = DBManager.getInstance();
		String sql = "SELECT * " + 
					 "FROM aes.student_exam se LEFT OUTER JOIN aes.exam_execution ee ON se.examCode = ee.code " +
					 "WHERE isApproved = 1 AND studentId=?;";
		try {
			PreparedStatement stmt = db.getConnection().prepareStatement(sql);
			stmt.setInt(1, studentId);
			ResultSet rs = stmt.executeQuery();
			while(rs.next()){
				StudentExam se = new StudentExam();
				se.setStudentId(studentId);
				se.setTimeStarted(new Date(rs.getTimestamp("timeStarted").getTime()));
				if (rs.getObject("timeFinished") != null)
					se.setTimeFinished(new Date(rs.getTimestamp("timeFinished").getTime()));
				if (rs.getObject("grade") != null)
					se.setGrade(rs.getInt("grade"));
				if (rs.getObject("notes") != null)
					se.setNotes(rs.getString("notes"));
				se.setApproved(true);
				se.setCheater(rs.getBoolean("isCheater"));
				if (rs.getObject("autoExamId") == null) {
					ManualExamExecution me = new ManualExamExecution();
					me.setCode(rs.getString("examCode"));
					se.setExam(me);
				}
				else {
					AutoExamExecution ee = new AutoExamExecution();
					ee.setCode(rs.getString("examCode"));
					se.setExam(ee);
				}
				arr.add(se);
			}
			rs.close();
			StudentExam[] ses = new StudentExam[arr.size()];
			arr.toArray(ses);
			return ses;
		}
		catch(SQLException ex){
			return null;
		}
	}


}